package com.demodis.dao.impl;

import java.util.ArrayList;
import java.util.List;

import com.demodis.dao.UserDaoI;
import com.demodis.domain.TblUser;
import com.demodis.util.DBUtil;

public class UserDaoImpl extends BaseDao implements UserDaoI {

	@Override
	public TblUser getUser(TblUser user) {
		String sql = "SELECT A.*, B.GROUPNAME, C.ROLENAME FROM TBL_USER A LEFT JOIN TBL_GROUP B ON A.GROUPID = B.GROUPID LEFT JOIN TBL_ROLE C ON A.ROLEID = C.ROLEID WHERE USERNAME = ? AND PASSWORD = ?;";
		TblUser tuser = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			rs = ps.executeQuery();
			if (rs.next()) {
				tuser = new TblUser();
				tuser.setUserId(rs.getInt("userId"));
				tuser.setGroupId(rs.getInt("groupId"));
				tuser.setGroupName(rs.getString("groupName"));
				tuser.setRoleId(rs.getInt("roleId"));
				tuser.setRoleName(rs.getString("roleName"));
				tuser.setUsername(rs.getString("username"));
				tuser.setUserRealName(rs.getString("userRealName"));
				tuser.setUsertype(rs.getString("usertype"));
				tuser.setUserstate(rs.getInt("userstate"));
				tuser.setDetered(rs.getInt("detered"));
				tuser.setPermTotalValue(rs.getInt("permTotalValue"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return tuser;
	}

	public List<TblUser> getUsers() {
		String sql = "select userId, userRealName from Tbl_user";
		List<TblUser> list = new ArrayList<TblUser>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				TblUser tuser = new TblUser();
				
				tuser.setUserId(rs.getInt("userId"));
				tuser.setUserRealName(rs.getString("userRealName"));
				
				list.add(tuser);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public List<TblUser> getUsers(TblUser user) {
		String sql = "SELECT A.*, B.GROUPNAME, C.ROLENAME FROM TBL_USER A LEFT JOIN TBL_GROUP B ON A.GROUPID = B.GROUPID LEFT JOIN TBL_ROLE C ON A.ROLEID = C.ROLEID WHERE A.realNameAbbr LIKE ? "+(user.getGroupId()==0 ? "" : "and A.groupId = ?")+" ORDER BY B.GROUPNAME LIMIT ?, ?;";
		List<TblUser> list = new ArrayList<TblUser>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%" + user.getQ() + "%");
			if(user.getGroupId() != 0) {
				ps.setInt(2, user.getGroupId());
			}
			ps.setInt(user.getGroupId()==0 ? 2 : 3, (user.getPage() - 1) * user.getRows());
			ps.setInt(user.getGroupId()==0 ? 3 : 4, user.getRows());
			rs = ps.executeQuery();

			while (rs.next()) {
				TblUser tuser = new TblUser();

				tuser.setUserId(rs.getInt("userId"));
				tuser.setGroupId(rs.getInt("groupId"));
				tuser.setGroupName(rs.getString("groupName"));
				tuser.setRoleName(rs.getString("roleName"));
				tuser.setRoleId(rs.getInt("roleId"));
				tuser.setUsername(rs.getString("username"));
				tuser.setUserRealName(rs.getString("userRealName"));
				tuser.setPassword(rs.getString("password"));
				tuser.setUsertype(rs.getString("usertype"));
				tuser.setUserstate(rs.getInt("userstate"));
				tuser.setDetered(rs.getInt("detered"));
				tuser.setPermTotalValue(rs.getInt("permTotalValue"));

				list.add(tuser);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public boolean save(TblUser user) {
		String sql = "INSERT INTO TBL_USER(GROUPID, USERNAME, PASSWORD, USERTYPE, USERSTATE, USERREALNAME, realNameAbbr) VALUES(?, ?, ?, ?, ?, ?, ?);";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, user.getGroupId());
			ps.setString(2, user.getUsername());
			ps.setString(3, user.getPassword());
			ps.setString(4, user.getUsertype());
			ps.setInt(5, user.getUserstate());
			ps.setString(6, user.getUserRealName());
			ps.setString(7, user.getRealNameAbbr());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1 ? true : false;
	}

	@Override
	public boolean modify(TblUser user) {
		String sql = "UPDATE TBL_USER SET GROUPID = ?, USERTYPE = ?, USERSTATE = ?, USERREALNAME = ?, realNameAbbr = ? WHERE USERID = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, user.getGroupId());
			ps.setString(2, user.getUsertype());
			ps.setInt(3, user.getUserstate());
			ps.setString(4, user.getUserRealName());
			ps.setString(5, user.getRealNameAbbr());
			ps.setInt(6, user.getUserId());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}

		return i == 1;
	}

	@Override
	public boolean delete(TblUser user) {
		String sql = "DELETE FROM TBL_USER WHERE USERID IN(" + user.getIds() + ");";
		int i = 0;
		try {
			conn = DBUtil.getConnection();

			ps = conn.prepareStatement(sql);
			i = ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i > 0 ? true : false;
	}

	@Override
	public int getUserNum(TblUser user) {
		String sql = "SELECT COUNT(*) FROM TBL_USER WHERE realNameAbbr LIKE ?" +(user.getGroupId() == 0 ? "" : " and groupId = ?");
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%" + user.getQ() + "%");
			if(user.getGroupId() != 0) {
				ps.setInt(2, user.getGroupId());
			}

			rs = ps.executeQuery();

			if (rs.next()) {
				i = rs.getInt(1);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i;
	}

	@Override
	public boolean deleteByGroupId(String groupIds) {
		String sql = "DELETE FROM TBL_USER WHERE GROUPID IN(" + groupIds + ");";
		try {
			conn = DBUtil.getConnection();

			ps = conn.prepareStatement(sql);
			ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return true;
	}

	@Override
	public boolean modifyDetered(int userId, int detered) {
		String sql = "UPDATE TBL_USER SET detered = ? WHERE USERID = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, detered);
			ps.setInt(2, userId);

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}

		return i == 1;
	}

	@Override
	public boolean modifyPass(TblUser user) {
		String sql = "UPDATE TBL_USER SET PASSWORD = ? WHERE USERID = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setString(1, user.getPassword());
			ps.setInt(2, user.getUserId());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}

		return i == 1;
	}

	@Override
	public List<TblUser> getUsersCU(TblUser user) {
		String sql = "SELECT A.*, B.GROUPNAME, C.ROLENAME FROM TBL_USER A LEFT JOIN TBL_GROUP B ON A.GROUPID = B.GROUPID LEFT JOIN TBL_ROLE C ON A.ROLEID = C.ROLEID WHERE B.GROUPID = ? AND A.USERNAME LIKE ? ORDER BY B.GROUPNAME LIMIT ?, ?;";
		List<TblUser> list = new ArrayList<TblUser>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, user.getGroupId());
			ps.setString(2, "%" + user.getQ() + "%");
			ps.setInt(3, (user.getPage() - 1) * user.getRows());
			ps.setInt(4, user.getRows());
			rs = ps.executeQuery();

			while (rs.next()) {
				TblUser tuser = new TblUser();

				tuser.setUserId(rs.getInt("userId"));
				tuser.setGroupId(rs.getInt("groupId"));
				tuser.setGroupName(rs.getString("groupName"));
				tuser.setRoleName(rs.getString("roleName"));
				tuser.setRoleId(rs.getInt("roleId"));
				tuser.setUsername(rs.getString("username"));
				tuser.setUserRealName(rs.getString("userRealName"));
				tuser.setPassword(rs.getString("password"));
				tuser.setUsertype(rs.getString("usertype"));
				tuser.setUserstate(rs.getInt("userstate"));
				tuser.setDetered(rs.getInt("detered"));
				tuser.setPermTotalValue(rs.getInt("permTotalValue"));

				list.add(tuser);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public int getUserNumCUG(TblUser user) {
		String sql = "SELECT COUNT(*) FROM TBL_USER WHERE groupId = ?";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, user.getGroupId());

			rs = ps.executeQuery();

			if (rs.next()) {
				i = rs.getInt(1);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i;
	}

	@Override
	public boolean userExist(String username) {
		boolean falg = false;
		String sql = "SELECT * FROM TBL_USER  WHERE USERNAME = ?;";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			rs = ps.executeQuery();
			if(rs.next()) {
				falg = true;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return falg;
	}
}
